﻿Public Class Form1
    Dim da As New Class1
    Dim ds As New DataSet
    Dim dt As New DataTable
    Dim ds2 As New DataSet
    Dim dt2 As New DataTable
    Dim strSql As String = ""
    Public x As Boolean
    Public y As Boolean
    Dim ct As New System.Globalization.CultureInfo("en-US")
   
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        x = True
        Form2.Show()
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        x = False
        Form2.Show()
    End Sub

    Private Sub Button9_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button9.Click
        y = True
        Form3.Show()
    End Sub

    Private Sub Button8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button8.Click
        y = False
        Form3.Show()
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ct.DateTimeFormat.ShortDatePattern = "MM/dd/yyyy"
        System.Threading.Thread.CurrentThread.CurrentCulture = ct
        strSql = "select * from video order by video_id"
        ds = da.PopulateList(strSql)
        dt = ds.Tables(0)
        DataGridView1.DataSource = dt
        DataGridView1.AllowUserToResizeRows = False
        For Each column As DataGridViewColumn In DataGridView1.Columns
            Select Case column.HeaderText
                Case "video_id"
                    column.HeaderText = "เลขทะเบียน"
                    column.AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
                Case "video_title"
                    column.HeaderText = "ชื่อเรื่อง"
                    column.AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
                Case "video_type"
                    column.HeaderText = "ประเภท"
                    column.AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
                Case "video_status"
                    column.HeaderText = "สถาณะ"
                    column.AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
            End Select
        Next
        DataGridView1.Show()
        TextBox5.Text = Date.Now.ToString("dd MMM yyyy hh:mm:ss")
    End Sub

    Private Sub TabControl1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles TabControl1.SelectedIndexChanged
        x = True
        y = True
        If TabControl1.SelectedIndex = 0 Then
            strSql = "select * from video order by video_id"
            ds = da.PopulateList(strSql)
            dt = ds.Tables(0)
            DataGridView1.DataSource = dt
            DataGridView1.Show()
        ElseIf TabControl1.SelectedIndex = 1 Then
            strSql = "select r.rent_id,b.cus_name,r.rent_pick,r.rent_return,v.video_title from rental as r join video as v on r.video_id = v.video_id join customer as b on r.cus_id = b.cus_id order by rent_id"
            ds = da.PopulateList(strSql)
            dt = ds.Tables(0)
            If dt.Rows.Count = 0 Then
                Exit Sub
            End If
            DataGridView2.DataSource = dt
            DataGridView2.AllowUserToResizeRows = False
            For Each column As DataGridViewColumn In DataGridView2.Columns
                Select Case column.HeaderText
                    Case "rent_id"
                        column.HeaderText = "เลขทะเบียน"
                    Case "cus_name"
                        column.HeaderText = "ชื่อสมาชิก"
                    Case "rent_pick"
                        column.HeaderText = "วันยืม"
                    Case "rent_return"
                        column.HeaderText = "วันคืน"
                    Case "video_title"
                        column.HeaderText = "ชื่อเรื่อง"
                        column.AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
                End Select
            Next
            DataGridView2.Show()

            strSql = "select * from customer where cus_name='" & DataGridView2.SelectedRows(0).Cells(1).Value & "' "
            ds2 = da.PopulateList(strSql)
            dt2 = ds2.Tables(0)
            If dt2.Rows.Count <> 0 Then
                TextBox1.Text = dt2.Rows(0)("cus_id")
                TextBox2.Text = dt2.Rows(0)("cus_name")
                TextBox3.Text = dt2.Rows(0)("cus_sname")
                TextBox4.Text = dt2.Rows(0)("cus_permission")
                TextBox6.Text = dt2.Rows(0)("cus_num")
                TextBox7.Text = dt2.Rows(0)("cus_permission") - dt2.Rows(0)("cus_num")
            End If

        Else
            strSql = "select * from customer order by cus_id"
            ds = da.PopulateList(strSql)
            dt = ds.Tables(0)
            DataGridView3.DataSource = dt
            DataGridView3.AllowUserToResizeRows = False
            For Each column As DataGridViewColumn In DataGridView3.Columns
                Select Case column.HeaderText
                    Case "cus_id"
                        column.HeaderText = "รหัสสมาชิก"
                    Case "cus_name"
                        column.HeaderText = "ชื่อสมาชิก"
                        column.AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
                    Case "cus_sname"
                        column.HeaderText = "นามสกุล"
                        column.AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
                    Case "cus_birthdate"
                        column.HeaderText = "วดป. เกิด"
                    Case "cus_permission"
                        column.HeaderText = "สิทธิ(เล่ม)"
                    Case "cus_num"
                        column.HeaderText = "ยืม(เล่ม)"
                End Select
            Next
            DataGridView3.Show()
        End If
    End Sub

    Private Sub DataGridView2_CellClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView2.CellClick
        Try
            strSql = "select * from customer where cus_name='" & DataGridView2.SelectedRows(0).Cells(1).Value & "' "
            ds2 = da.PopulateList(strSql)
            dt2 = ds2.Tables(0)
            If dt2.Rows.Count <> 0 Then
                TextBox1.Text = dt2.Rows(0)("cus_id")
                TextBox2.Text = dt2.Rows(0)("cus_name")
                TextBox3.Text = dt2.Rows(0)("cus_sname")
                TextBox4.Text = dt2.Rows(0)("cus_permission")
                TextBox6.Text = dt2.Rows(0)("cus_num")
                TextBox7.Text = dt2.Rows(0)("cus_permission") - dt2.Rows(0)("cus_num")
            End If
        Catch ex As ArgumentOutOfRangeException
            Console.WriteLine("Exception: " & ex.Message)
            Console.WriteLine(ex.StackTrace)
        End Try


    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        If MsgBox("คุณต้องการลบข้อมูลนี้ใช่หรือไม่", MsgBoxStyle.Exclamation + MsgBoxStyle.YesNo) = MsgBoxResult.No Then
            Exit Sub
        End If
        strSql = "delete from video where video_id='" & DataGridView1.SelectedRows(0).Cells(0).Value & "'"
        da.PopulateList(strSql)
        strSql = "select * from video order by video_id"
        ds = da.PopulateList(strSql)
        dt = ds.Tables(0)
        DataGridView1.DataSource = dt
        DataGridView1.AllowUserToResizeRows = False
        For Each column As DataGridViewColumn In DataGridView1.Columns
            Select Case column.HeaderText
                Case "video_id"
                    column.HeaderText = "เลขทะเบียน"
                    column.AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
                Case "video_title"
                    column.HeaderText = "ชื่อเรื่อง"
                    column.AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
                Case "video_type"
                    column.HeaderText = "ประเภท"
                    column.AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
                Case "video_status"
                    column.HeaderText = "สถาณะ"
                    column.AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
            End Select
        Next
        DataGridView1.Show()
    End Sub

    Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
        If MsgBox("คุณต้องการลบข้อมูลนี้ใช่หรือไม่", MsgBoxStyle.Exclamation + MsgBoxStyle.YesNo) = MsgBoxResult.No Then
            Exit Sub
        End If
        strSql = "delete from customer where cus_id='" & DataGridView3.SelectedRows(0).Cells(0).Value & "' "
        da.PopulateList(strSql)
        strSql = "select * from customer order by cus_id"
        ds = da.PopulateList(strSql)
        dt = ds.Tables(0)
        DataGridView3.DataSource = dt
        DataGridView3.AllowUserToResizeRows = False
        For Each column As DataGridViewColumn In DataGridView3.Columns
            Select Case column.HeaderText
                Case "cus_id"
                    column.HeaderText = "รหัสสมาชิก"
                Case "cus_name"
                    column.HeaderText = "ชื่อสมาชิก"
                    column.AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
                Case "cus_sname"
                    column.HeaderText = "นามสกุล"
                    column.AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
                Case "cus_birthdate"
                    column.HeaderText = "วดป. เกิด"
                Case "cus_permission"
                    column.HeaderText = "สิทธิ(เล่ม)"
                Case "cus_num"
                    column.HeaderText = "ยืม(เล่ม)"
            End Select
        Next
        DataGridView3.Show()
    End Sub

    Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
        TextBox5.Text = Date.Now.ToString("dd MMM yyyy hh:mm:ss")
    End Sub

    Sub Button5Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button5.Click
        Form4.Show()
    End Sub

    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
        strSql = "select * from rental"
        ds = da.PopulateList(strSql)
        dt = ds.Tables(0)
        If dt.Rows.Count = 0 Then
            MsgBox("ได้ทำการคืนหมดเรียบร้อยแล้ว", MsgBoxStyle.OkOnly, "การคืนวิดีโอ")
            Exit Sub
        End If
        strSql = "select * from rental where rent_id='" & DataGridView2.SelectedRows(0).Cells(0).Value & "' "
        ds = da.PopulateList(strSql)
        dt = ds.Tables(0)
        strSql = "update video set video_status='Available' where video_id='" & dt.Rows(0)("video_id") & "' "
        da.PopulateList(strSql)
        strSql = "select cus_num from customer where cus_id='" & dt.Rows(0)("cus_id") & "'"
        ds2 = da.PopulateList(strSql)
        dt2 = ds2.Tables(0)
        strSql = "update customer set cus_num='" & dt2.Rows(0)("cus_num") - 1 & "' where cus_id='" & dt.Rows(0)("cus_id") & "' "
        da.PopulateList(strSql)
        If dt.Rows(0)("rent_return") < Date.Now Then
            MsgBox("ส่งเกินกำหนดเป็นเวลา = " & Date.Now.Day - Convert.ToDateTime(dt.Rows(0)("rent_return")).Day & "วัน" & vbNewLine & "ค่าปรับ = " & (Date.Now.Day - Convert.ToDateTime(dt.Rows(0)("rent_return")).Day) * 30 & "บาท")

        End If
        strSql = "delete from rental where rent_id='" & DataGridView2.SelectedRows(0).Cells(0).Value & "'"
        da.PopulateList(strSql)
        MsgBox("ทำการคืนวิดีโอเสร็จสิ้นเรียบร้อยแล้ว", MsgBoxStyle.OkOnly, "การคืนวิดีโอ")
        strSql = "select r.rent_id,b.cus_name,r.rent_pick,r.rent_return,v.video_title from rental as r join video as v on r.video_id = v.video_id join customer as b on r.cus_id = b.cus_id order by rent_id"
        ds = da.PopulateList(strSql)
        dt = ds.Tables(0)
        If dt.Rows.Count <> 0 Then
            DataGridView2.DataSource = dt
            DataGridView2.AllowUserToResizeRows = False
            For Each column As DataGridViewColumn In DataGridView2.Columns
                Select Case column.HeaderText
                    Case "rent_id"
                        column.HeaderText = "เลขทะเบียน"
                    Case "cus_name"
                        column.HeaderText = "ชื่อสมาชิก"
                    Case "rent_pick"
                        column.HeaderText = "วันยืม"
                    Case "rent_return"
                        column.HeaderText = "วันคืน"
                    Case "video_title"
                        column.HeaderText = "ชื่อเรื่อง"
                        column.AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
                End Select
            Next
            DataGridView2.Show()
        Else
            TextBox1.Clear()
            TextBox2.Clear()
            TextBox3.Clear()
            TextBox4.Clear()
            TextBox6.Clear()
            TextBox7.Clear()
            ds.Clear()
            DataGridView2.DataSource = ds
            DataGridView2.Show()
        End If

    End Sub


    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim s As String = InputBox("กรุณาใส่ชื่อสมาชิก")
        If String.IsNullOrEmpty(s) = True Then
            Exit Sub
        End If
        strSql = "select r.rent_id,b.cus_name,r.rent_pick,r.rent_return,v.video_title from rental as r join video as v on r.video_id = v.video_id join customer as b on r.cus_id = b.cus_id where b.cus_name='" & s & "' order by rent_id"
        ds = da.PopulateList(strSql)
        dt = ds.Tables(0)
        If dt.Rows.Count = 0 Then
            MsgBox("ไม่มีข้อมูลสมาชิก", 0, "ข้อผิดพลาด")
            Exit Sub
        End If
        DataGridView2.DataSource = dt
        DataGridView2.AllowUserToResizeRows = False
        For Each column As DataGridViewColumn In DataGridView2.Columns
            Select Case column.HeaderText
                Case "rent_id"
                    column.HeaderText = "เลขทะเบียน"
                Case "cus_name"
                    column.HeaderText = "ชื่อสมาชิก"
                Case "rent_pick"
                    column.HeaderText = "วันยืม"
                Case "rent_return"
                    column.HeaderText = "วันคืน"
                Case "video_title"
                    column.HeaderText = "ชื่อเรื่อง"
                    column.AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
            End Select
        Next
        DataGridView2.Show()

    End Sub
End Class
